package com.apes.scm.pur.purchaseOrder.repository;


import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.scm.pur.purchaseOrder.model.PurchaseOrder;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Map;

/**
 * @author xul
 * @create 2018-01-23 16:07
 */
public interface PurchaseOrderRepository extends CustomRepository<PurchaseOrder, String> {

    @Query(value = "" +
            "SELECT PRODUCT_ID, SUM(PRODUCT_UOM_QTY - STOP_QTY) PURCHASE_QTY, " +
            "       (SELECT REQUIRE_QTY " +
            "           FROM V_PURCHASE_COMPARE_QTY J " +
            "          WHERE J.PRODUCT_REQUIRE_ID = A.PRODUCT_REQUIRE_ID " +
            "            AND J.PRODUCT_ID = B.PRODUCT_ID) REQUIRE_QTY " +
            "  FROM PUR_PURCHASE_ORDER A, PUR_PURCHASE_ORDER_ITEM B " +
            " WHERE A.ID = B.PURCHASE_ID AND A.STATE != 'delete' AND STOP_QTY = 0 " +
            "   AND PRODUCT_REQUIRE_ID = ?1 " +
            "   AND PRODUCT_ID = ?2 " +
            " GROUP BY PRODUCT_ID, PRODUCT_REQUIRE_ID ", nativeQuery = true)
    List<Map> findAllRequireProQty(String requireId, String productId);
    
    /**
     * fetch data by dept id and scene id and state
     *
     * @param sceneId 场景编码
     * @param state 状态值
     * @param pageable 分页条件
     * @return  Page<InventoryBatch>
     */
    @Query("select t from PurchaseOrder t where  t.scene.id =?1 and t.state= ?2 and t.company.id=?3")
    Page<PurchaseOrder> findAllBySceneAndStateAndCompany( String sceneId, String state,String companyId, Pageable pageable);

    @Query("select t from PurchaseOrder t where  t.scene.id =?1 and t.state= ?2 and t.company.id=?3 and t.id =?4")
    Page<PurchaseOrder> findOnePurchase(String sceneId, String state, String companyId, String id, Pageable pageable);

    @Query("select t from PurchaseOrder t where  t.scene.id =?2 and t.state= ?3 and (t.id=?1 or t.origin=?1 or t.source=?1 or t.supplierRef=?1) and t.company.id=?4")
    Page<PurchaseOrder> findAllByIdAndSceneAndStateAndCompany(String id, String sceneId, String state,String companyId,  Pageable pageable);

    @Query("select t from PurchaseOrder t inner join t.purchaseItems f where  t.scene.id =?2 and t.state= ?3 and f.product.id=?1 and t.company.id=?4")
    Page<PurchaseOrder> findAllByProductAndSceneAndStateAndCompany(String productId, String sceneId, String state,String companyId,  Pageable pageable);

    @Query("select t from PurchaseOrder t inner join t.purchaseItems f where  t.scene.id =?3 and t.state= ?4 and f.product.id=?2 and (t.id=?1 or t.origin=?1 or t.source=?1 or t.supplierRef=?1) and t.company.id=?5")
    Page<PurchaseOrder> findAllByIdAndProductAndSceneAndStateAndCompany(String id, String productId, String sceneId, String state,String companyId,  Pageable pageable);

    @Modifying
    @Query(value = " UPDATE PUR_PURCHASE_ORDER A SET STATE = 'confirmed', VERSION = VERSION + 1 WHERE A.ID = ?1 ", nativeQuery = true)
    void updateState(@Param("id")String id);

    @Modifying
    @Query(value = " UPDATE PUR_PURCHASE_ORDER_ITEM A " +
            "   SET QTY_RECEIVED = QTY_RECEIVED - " +
            "                       (SELECT QTY " +
            "                          FROM PUR_REDEMPTION_ITEM J, PUR_REDEMPTION K " +
            "                         WHERE J.ID = K.REDEMPTION_ID " +
            "                           AND K.ID = ?2 " +
            "                           AND J.PRODUCT_ID = A.PRODUCT_ID) " +
            " WHERE A.PURCHASE_ID = ?1  ", nativeQuery = true)
    void updateNumber(@Param("id")String id, @Param("redemptionId")String redemptionId);

    @Query(value = "SELECT * FROM PUR_PURCHASE_ORDER WHERE ID = :id ", nativeQuery = true)
    PurchaseOrder waybillPurchaseOrder(@Param("id")String id);

    @Query(value = "SELECT T.*, T.ROWID FROM PUR_PURCHASE_ORDER T WHERE PRODUCT_REQUIRE_ID = ?1 " +
            "         AND (SUPPLIER_ID = '007011' OR SUPPLIER_ID = '000286') AND STATE NOT IN ('cancel', 'delete') " +
            "         AND SUPPLIER_ID IN (SELECT VALUE FROM MAS_BUSINESS_PARAM WHERE QUERY_KEY = 'baturuSupplier') AND ROWNUM = 1 " +
            "        ORDER BY T.CREATE_DATE DESC ", nativeQuery = true)
    PurchaseOrder findBaturuPurchaseOrder(String requireId);

    PurchaseOrder findBysupplierRef(@Param("supplierRef") String supplierRef);
}
